<?php

class CategoryMapper extends DataMapper
{
    const MODEL_CLASS = 'Category';

    // ------------- 更新系クエリ -----------------

    function insert(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        $modelClass = static::MODEL_CLASS;

        $stmt = $pdo->prepare('
            INSERT INTO category(slug, name, note, editor_note, mode, sort)
            VALUES (?, ?, ?, ?, ?, ?)
        ');
        $stmt->bindParam(1, $slug,  PDO::PARAM_STR);
        $stmt->bindParam(2, $name,  PDO::PARAM_STR);
        $stmt->bindParam(3, $note,  PDO::PARAM_STR);
        $stmt->bindParam(4, $enote, PDO::PARAM_STR);
        $stmt->bindParam(5, $mode,  PDO::PARAM_INT);
        $stmt->bindParam(6, $sort,  PDO::PARAM_INT);

        if (! is_array($data)) $data = array($data);

        foreach ($data as $row) {
            if (! $row instanceof $modelClass || $row->isValid()) {
                throw new InvalidArgumentException;
            }
            $slug  = $row->slug;
            $name  = $row->name;
            $note  = $row->note;
            $enote = $row->editor_note;
            $mode  = $row->mode;
            $sort  = $row->sort;
            $stmt->execute();

            $id = $pdo->lastInsertId('category_category_id_seq');
            $this->updateRelation($id, $row->relation);
        }
    }

    function update(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        
        $modelClass = static::MODEL_CLASS;

        $stmt = $pdo->prepare('
            UPDATE category
               SET slug        = ?,
                   name        = ?,
                   note        = ?,
                   editor_note = ?,
                   mode        = ?,
                   sort        = ?,
                   update_date = now()
             WHERE category_id = ?
        ');
        $stmt->bindParam(1, $slug,  PDO::PARAM_STR);
        $stmt->bindParam(2, $name,  PDO::PARAM_STR);
        $stmt->bindParam(3, $note,  PDO::PARAM_STR);
        $stmt->bindParam(4, $enote, PDO::PARAM_STR);
        $stmt->bindParam(5, $mode,  PDO::PARAM_INT);
        $stmt->bindParam(6, $sort,  PDO::PARAM_INT);
        $stmt->bindParam(7, $id,    PDO::PARAM_INT);
        
        if (! is_array($data)) $data = array($data);

        foreach ($data as $row) {
            if (! $row instanceof $modelClass || $row->isValid()) {
                throw new InvalidArgumentException;
            }
            $slug  = $row->slug;
            $name  = $row->name;
            $note  = $row->note;
            $enote = $row->editor_note;
            $mode  = $row->mode;
            $sort  = $row->sort;
            $id    = $row->category_id;
            $stmt->execute();
            
            $this->updateRelation($row->category_id, $row->relation);
        }
    }

    private function updateRelation($id, $relations)
    {
        $pdo = $this->getPDO();

        $stmt = $pdo->prepare('
            DELETE FROM category_relation_list
             WHERE category_id = ?
        ');
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
        $stmt->execute();
        
        if (!$relations) return;
        
        $stmt = $pdo->prepare('
            INSERT INTO category_relation_list(category_id, relation)
            VALUES (?, ?)
        ');
        $stmt->bindParam(1, $id,    PDO::PARAM_INT);
        $stmt->bindParam(2, $value, PDO::PARAM_STR);

        if (! is_array($relations)) $relations = array($relations);

        foreach ($relations as $v) {
            $value = $v;
            $stmt->execute();
        }
    }




    function delete(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        
        $modelClass = static::MODEL_CLASS;

        $stmt = $pdo->prepare('
            DELETE FROM category
             WHERE category_id = ?
        ');
        $stmt->bindParam(1, $id, PDO::PARAM_INT);

        if (! is_array($data)) $data = array($data);
        
        foreach ($data as $row) {
            if (! $row instanceof $modelClass) {
                throw new InvalidArgumentException;
            }
            $id = $row->category_id;
            $result = $stmt->execute();
        }
    }



    function insertChild(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        $modelClass = static::MODEL_CLASS;

        $stmt = $pdo->prepare('
            INSERT INTO category_list(category_id, slug, name, sort, param_count, note, editor_note)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ');
        $stmt->bindParam(1, $category_id,  PDO::PARAM_INT);
        $stmt->bindParam(2, $slug,  PDO::PARAM_STR);
        $stmt->bindParam(3, $name,  PDO::PARAM_STR);
        $stmt->bindParam(4, $sort,  PDO::PARAM_INT);
        $stmt->bindParam(5, $param, PDO::PARAM_INT);
        $stmt->bindParam(6, $note,  PDO::PARAM_STR);
        $stmt->bindParam(7, $enote, PDO::PARAM_STR);

        if (! is_array($data)) $data = array($data);

        foreach ($data as $row) {
            if (! $row instanceof $modelClass || $row->isValid()) {
                throw new InvalidArgumentException;
            }
            $category_id  = $row->category_id;
            $slug  = $row->slug;
            $name  = $row->name;
            $sort  = $row->sort;
            $param = $row->param_count;
            $note  = $row->note;
            $enote = $row->editor_note;
            $stmt->execute();
        }
    }


    function updateChild(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        
        $modelClass = static::MODEL_CLASS;

        $stmt = $pdo->prepare('
            UPDATE category_list
               SET slug         = ?,
                   name          = ?,
                   sort          = ?,
                   param_count   = ?,
                   note          = ?,
                   editor_note   = ?,
                   update_date   = now()
             WHERE category_list_id = ?
        ');
        $stmt->bindParam(1, $slug, PDO::PARAM_STR);
        $stmt->bindParam(2, $name,  PDO::PARAM_STR);
        $stmt->bindParam(3, $sort,  PDO::PARAM_INT);
        $stmt->bindParam(4, $param, PDO::PARAM_INT);
        $stmt->bindParam(5, $note,  PDO::PARAM_STR);
        $stmt->bindParam(6, $enote, PDO::PARAM_STR);
        $stmt->bindParam(7, $id,    PDO::PARAM_INT);

        if (! is_array($data)) $data = array($data);

        foreach ($data as $row) {
            if (! $row instanceof $modelClass || $row->isValid()) {
                throw new InvalidArgumentException;
            }
            $slug  = $row->slug;
            $name  = $row->name;
            $sort  = $row->sort;
            $param = $row->param_count;
            $note  = $row->note;
            $enote = $row->editor_note;
            $id    = $row->category_list_id;
            $stmt->execute();
        }
    }


    function deleteChild(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        
        $modelClass = static::MODEL_CLASS;

        $stmt = $pdo->prepare('
            DELETE FROM category_list
             WHERE category_id = ?
        ');
        $stmt->bindParam(1, $category_id, PDO::PARAM_INT);

        if (! is_array($data)) {
            $data = array($data);
        }
        foreach ($data as $row) {
            if (! $row instanceof $modelClass) {
                throw new InvalidArgumentException;
            }
            $category_id = $row->category_id;
            $result = $stmt->execute();
        }
    }


    
    function editLink(modelLoader $loader, $data)
    {
        if (! is_array($data)) $data = array($data);
        foreach ($data as $row) {
            if (! $row instanceof $modelClass || $row->isValid()) {
                throw new InvalidArgumentException;
            }
            $this->updateCategory('member', $loader->solo, $row->_category);
        }
    }
    
    function updatePostCategory(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        $modelClass = static::MODEL_CLASS;
        
        $target_id = $loader->solo;

        $stmt = $pdo->prepare('
        INSERT INTO post_category_list
               (history_id, category_id, category_list_id,
                sort,
                parameter_1, parameter_2, parameter_3, parameter_4, parameter_5, parameter_6)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ');
        $stmt->bindParam(1, $history_id,        PDO::PARAM_INT);
        $stmt->bindParam(2, $category_id,       PDO::PARAM_INT);
        $stmt->bindParam(3, $category_list_id,  PDO::PARAM_INT);
        $stmt->bindParam(4, $sort,        PDO::PARAM_INT);
        $stmt->bindParam(5, $column1,     PDO::PARAM_STR);
        $stmt->bindParam(6, $column2,     PDO::PARAM_STR);
        $stmt->bindParam(7, $column3,     PDO::PARAM_STR);
        $stmt->bindParam(8, $column4,     PDO::PARAM_STR);
        $stmt->bindParam(9, $column5,     PDO::PARAM_STR);
        $stmt->bindParam(10,$column6,     PDO::PARAM_STR);

        foreach ($data->_category as $category_count => $parameters) {
            
            $category_id      = $parameters->category_id;
            $category_list_id = $parameters->category_list_id;
            
            if (!$category_list_id) continue;

            $history_id       = $pdo->lastInsertId('post_history_history_id_seq');
            
            $sort = $category_count;

            for ($i = 1; $i <= 6; $i++) {
                $v = 'parameter_'.$i;
                $x = 'column'.$i;
                if (isset($parameters->$v)) {
                    $$x = $parameters->$v;
                }
            }
            $stmt->execute();
        }
    }

    //------------- 参照系クエリ ----------------

    function findById(modelLoader $loader)
    {
        $pdo = $this->getPDO();
        
        $sql = '
            SELECT category_id, slug, name, note, editor_note, update_date, mode, sort,
                   ARRAY(SELECT relation FROM category_relation_list relation WHERE relation.category_id = category.category_id ) relation
              FROM category
             WHERE category_id = ?
        ';
        
        $stmt = $pdo->prepare($sql);
        $id = $loader->solo;
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
        $stmt->execute();

        $this->_decorate($stmt, $sql);
        return $stmt->fetch();
    }

    function findBySlug(modelLoader $loader)
    {
        $pdo = $this->getPDO();
        
        $sql = '
            SELECT category_id, name
              FROM category
             WHERE slug = ?
        ';
        $stmt = $pdo->prepare($sql);
        $slug = $loader->solo;
        $stmt->bindParam(1, $slug, PDO::PARAM_STR);
        $stmt->execute();

        $this->_decorate($stmt, $sql);
        return $stmt->fetch();
    }

    function find(modelLoader $loader, $countMode = false)
    {
        $pdo = $this->getPDO();
        
        $order = $this->getOrderString($loader->order);
        $where = $this->getSearchString($loader->alike, 'ilike', array('slug','name','note','editor_note'), $pdo);
        $limit = $this->getLimitString($loader->limit, $loader->offset);

        $query = (!$countMode)
            ? $query = '
            SELECT category_id, slug, name, note, editor_note, update_date, mode, sort,
                   (SELECT count(category_id) FROM category_list list WHERE list.category_id = category.category_id) total,
                   ARRAY(SELECT relation
                           FROM category_relation_list relation
                          WHERE relation.category_id = category.category_id ) relation
              FROM category
                  '.$where.'
                  '.$order.'
                  '.$limit.'
            '
            : $query = '
                SELECT count(category_id) as _count,
                       '.$loader->limit.' as _limit
                  FROM category
                  '.$where.'
            ';
            
        $stmt = $pdo->query($query);
        $stmt->execute();
        $this->_decorate($stmt, $query);
        
        if (!$countMode) return $stmt;
        return $stmt->fetch();
    }


    function findChildById(modelLoader $loader)
    {
        $pdo = $this->getPDO();
        
        $sql = '
            SELECT
                   category_list_id, slug, name, sort, param_count, note, editor_note
              FROM category_list
             WHERE category_list_id = ?
        ';
        
        $stmt = $pdo->prepare($sql);
        $id = $loader->solo;
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
        $stmt->execute();

        $this->_decorate($stmt, $sql);
        return $stmt->fetch();
    }

    function findChild(modelLoader $loader, $countMode = false)
    {
        $pdo = $this->getPDO();
        $slug = @$loader->mapperParameters['slug'];
        
        
        if ($slug) {
            $where = '
                   category_id = 
                   (SELECT category.category_id
                      FROM category
                     WHERE category.slug = '.$pdo->quote($slug,PDO::PARAM_STR).')';
        } 
        
        $order = $this->getOrderString($loader->order);
        $where = $this->getSearchString($loader->alike, 'ilike', array('name','slug','note','editor_note'), $pdo, $where);
        $limit = $this->getLimitString($loader->limit, $loader->offset);

        $query = (!$countMode)
            ? $query = '
            SELECT category_list_id, sort, slug, name, param_count, note, editor_note, update_date
              FROM category_list
                  '.$where.'
                  '.$order.'
                  '.$limit.'
            '
            : $query = '
                SELECT count(category_id) as _count,
                       '.$loader->limit.' as _limit
                  FROM category_list
                  '.$where.'
            ';
        $stmt = $pdo->query($query);
        $stmt->execute();
        $this->_decorate($stmt, $query);
        
        if (!$countMode) return $stmt;
        return $stmt->fetch();
    }


    function findParameterByPost(modelLoader $loader, $history_id = null)
    {
        $pdo = $this->getPDO();
        
        $member = @$loader->mapperParameters['member'];
        
        $history_id = @$loader->mapperParameters['history_id'];
        $where = ($history_id)
            ? "AND post_history.history_id = ?"
            : "";

        $sql = "
            SELECT
                   DISTINCT ON (list.category_list_id)
                   post.category_id,
                   list.slug,
                   list.category_list_id,
                   list.sort,
                   parameter_1, parameter_2, parameter_3,
                   parameter_4, parameter_5, parameter_6,
                   (SELECT mode FROM category WHERE category.category_id = list.category_id) as mode
              FROM post_category_list post
              LEFT JOIN category_list list ON post.category_id = list.category_id AND list.category_list_id = post.category_list_id
             WHERE post.history_id = (SELECT max(history_id) FROM post_history WHERE post_history.post_id = ? {$where})
        ";
        $stmt = $pdo->prepare($sql);
        $id = $loader->solo;
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
        if ($history_id) {
            $stmt->bindParam(2, $history_id, PDO::PARAM_INT);
        }
        $stmt->execute();

        $this->_decorate($stmt, $sql);
        return $stmt;
    }

/*
    function findParameterHistoryByPost(modelLoader $loader)
    {
        return $this->findParameterByPost($loader, $loader->solo);
    }
*/
    function findByPost(modelLoader $loader)
    {
        $pdo = $this->getPDO();
        
        $sql = '
            SELECT
                   category.category_id, category.slug, category.name, category.note as editor_note,
                   child.slug, child.name as note, child.param_count, child.category_list_id, mode
              FROM category category
              LEFT JOIN category_list child ON child.category_id = category.category_id
             WHERE category.category_id IN (SELECT relation.category_id FROM category_relation_list relation WHERE relation = ?)
                   AND child.category_list_id is not null
                   ORDER BY category.sort, category.slug, child.sort
        ';
        
        $stmt = $pdo->prepare($sql);
        $id = $loader->solo;
        $stmt->bindParam(1, $id, PDO::PARAM_STR);
        $stmt->execute();

        $this->_decorate($stmt, $sql);
        return $stmt;
    }

}